<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Query Filter - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span></li>
<li class="tocentry current"><a class="current" href="QueryFilter.htm">Query Filter</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>



</ul>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></li> / <li><a href="QueryFilter.htm">Query Filter</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="query-filter">Query filter<a class="headerlink" href="#query-filter" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>This scenario demonstrate how to get rows from database with Entity Framework Core's query filter feature. This is for
Multi-Tenancy scenarios.</p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IQueryFilterScenario&lt;TStudent&gt;
    where TStudent : class, IStudent, new()
{
    IList&lt;TStudent&gt; GetStudents(int schoolId);
}
</code></pre>

<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>A query filter is setup in the OnModelCreating event of the DB Context.</p>
<pre><code class="cs">protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    if (modelBuilder == null)
        throw new ArgumentNullException(nameof(modelBuilder), $&quot;{nameof(modelBuilder)} is null.&quot;);

    base.OnModelCreating(modelBuilder);

    //The ISchool interface is applied to all entities that need to be filtered by tenant.
    modelBuilder.SetQueryFilterOnAllEntities&lt;ISchool&gt;(s =&gt; s.SchoolId == SchoolId);
}
</code></pre>

<p>Normally this would be done for each table. But by placing a marker interface (<code>ISchool</code> in this case) and some helper methods, it can be automatically applied to all tables.</p>
<pre><code class="cs">/*
Copyright Phil Haack
Licensed under the MIT license - https://github.com/haacked/CodeHaacks/blob/main/LICENSE.
*/
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Query;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace Recipes.EntityFrameworkCore.QueryFilter.Helpers
{


    public static class ModelBuilderExtensions
    {
        static readonly MethodInfo SetQueryFilterMethod = typeof(ModelBuilderExtensions)
                .GetMethods(BindingFlags.NonPublic | BindingFlags.Static)
                .Single(t =&gt; t.IsGenericMethod &amp;&amp; t.Name == nameof(SetQueryFilter));

        public static void SetQueryFilterOnAllEntities&lt;TEntityInterface&gt;(
            this ModelBuilder builder,
            Expression&lt;Func&lt;TEntityInterface, bool&gt;&gt; filterExpression)
        {
            foreach (var type in builder.Model.GetEntityTypes()
                .Where(t =&gt; t.BaseType == null)
                .Select(t =&gt; t.ClrType)
                .Where(t =&gt; typeof(TEntityInterface).IsAssignableFrom(t)))
            {
                builder.SetEntityQueryFilter(
                    type,
                    filterExpression);
            }
        }

        static void SetEntityQueryFilter&lt;TEntityInterface&gt;(
            this ModelBuilder builder,
            Type entityType,
            Expression&lt;Func&lt;TEntityInterface, bool&gt;&gt; filterExpression)
        {
            SetQueryFilterMethod
                .MakeGenericMethod(entityType, typeof(TEntityInterface))
               .Invoke(null, new object[] { builder, filterExpression });
        }

        static void SetQueryFilter&lt;TEntity, TEntityInterface&gt;(
            this ModelBuilder builder,
            Expression&lt;Func&lt;TEntityInterface, bool&gt;&gt; filterExpression)
                where TEntityInterface : class
                where TEntity : class, TEntityInterface
        {
            var concreteExpression = filterExpression
                .Convert&lt;TEntityInterface, TEntity&gt;();
            builder.Entity&lt;TEntity&gt;()
                .AppendQueryFilter(concreteExpression);
        }

        // CREDIT: This comment by magiak on GitHub https://github.com/dotnet/efcore/issues/10275#issuecomment-785916356
        static void AppendQueryFilter&lt;T&gt;(this EntityTypeBuilder entityTypeBuilder, Expression&lt;Func&lt;T, bool&gt;&gt; expression)
            where T : class
        {
            var parameterType = Expression.Parameter(entityTypeBuilder.Metadata.ClrType);

            var expressionFilter = ReplacingExpressionVisitor.Replace(
                expression.Parameters.Single(), parameterType, expression.Body);

            var currentQueryFilter = entityTypeBuilder.Metadata.GetQueryFilter();
            if (currentQueryFilter != null)
            {
                var currentExpressionFilter = ReplacingExpressionVisitor.Replace(
                    currentQueryFilter.Parameters.Single(), parameterType, currentQueryFilter.Body);
                expressionFilter = Expression.AndAlso(currentExpressionFilter, expressionFilter);
            }

            var lambdaExpression = Expression.Lambda(expressionFilter, parameterType);
            entityTypeBuilder.HasQueryFilter(lambdaExpression);
        }
    }


}

</code></pre>

<pre><code class="cs">// Copyright (c) 2020 Phil Haack, GitHub: haacked
// https://gist.github.com/haacked/febe9e88354fb2f4a4eb11ba88d64c24

using System;
using System.Collections.ObjectModel;
using System.Linq;
using System.Linq.Expressions;

namespace Recipes.EntityFrameworkCore.QueryFilter.Helpers
{
    public static class ExpressionExtensions
    {
        // This magic is courtesy of this StackOverflow post.
        // https://stackoverflow.com/questions/38316519/replace-parameter-type-in-lambda-expression
        // I made some tweaks to adapt it to our needs - @haacked
        public static Expression&lt;Func&lt;TTarget, bool&gt;&gt; Convert&lt;TSource, TTarget&gt;(
            this Expression&lt;Func&lt;TSource, bool&gt;&gt; root)
        {
            var visitor = new ParameterTypeVisitor&lt;TSource, TTarget&gt;();
            return (Expression&lt;Func&lt;TTarget, bool&gt;&gt;)visitor.Visit(root);
        }

        class ParameterTypeVisitor&lt;TSource, TTarget&gt; : ExpressionVisitor
        {
            private ReadOnlyCollection&lt;ParameterExpression&gt;? _parameters;

            protected override Expression VisitParameter(ParameterExpression node)
            {
                return _parameters?.FirstOrDefault(p =&gt; p.Name == node.Name)
                       ?? (node.Type == typeof(TSource) ? Expression.Parameter(typeof(TTarget), node.Name) : node);
            }

            protected override Expression VisitLambda&lt;T&gt;(Expression&lt;T&gt; node)
            {
                _parameters = VisitAndConvert(node.Parameters, &quot;VisitLambda&quot;);
                return Expression.Lambda(Visit(node.Body), _parameters);
            }
        }
    }
}

</code></pre>

<p>This example shows it being used.</p>
<pre><code class="cs">public class QueryFilterScenario : IQueryFilterScenario&lt;Student&gt;
{
    private readonly Func&lt;int, OrmCookbookContextWithQueryFilter&gt; CreateFilteredDbContext;

    public QueryFilterScenario(Func&lt;int, OrmCookbookContextWithQueryFilter&gt; dBContextFactory)
    {
        CreateFilteredDbContext = dBContextFactory;
    }

    public IList&lt;Student&gt; GetStudents(int schoolId)
    {
        using (var context = CreateFilteredDbContext(schoolId))
        {
            //SchoolId filter is automatically applied
            return context.Students.OrderBy(s =&gt; s.Name).ToList();
        }
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
